<?php
/**
 * Static content controller.
 *
 * This file will render views from views/pages/
 *
 * PHP versions 4 and 5
 *
 * CakePHP(tm) : Rapid Development Framework (http://cakephp.org)
 * Copyright 2005-2010, Cake Software Foundation, Inc. (http://cakefoundation.org)
 *
 * Licensed under The MIT License
 * Redistributions of files must retain the above copyright notice.
 *
 * @copyright     Copyright 2005-2010, Cake Software Foundation, Inc. (http://cakefoundation.org)
 * @link          http://cakephp.org CakePHP(tm) Project
 * @package       cake
 * @subpackage    cake.cake.libs.controller
 * @since         CakePHP(tm) v 0.2.9
 * @license       MIT License (http://www.opensource.org/licenses/mit-license.php)
 */

/**
 * Static content controller
 *
 * Override this controller by placing a copy in controllers directory of an application
 *
 * @package       cake
 * @subpackage    cake.cake.libs.controller
 * @link http://book.cakephp.org/view/958/The-Pages-Controller
 */
class PagesController extends AppController {

/**
 * Controller name
 *
 * @var string
 * @access public
 */
	var $name = 'Pages';

/**
 * Default helper
 *
 * @var array
 * @access public
 */

	var $helpers = array('Html', 'Session');

/**
 * This controller does not use a model
 *
 * @var array
 * @access public
 */
	var $uses = array('ClientsUser','User','Client','ProductsUser','Country','Specialty','SpecialtiesUser','Sucursal');


/**
 * Displays a view
 *
 * @param mixed What page to display
 * @access public
 */
	function display() {


		$path = func_get_args();

		$count = count($path);
		if (!$count) {
			$this->redirect('/dashboards');
		}
		$page = $subpage = $title_for_layout = null;


		if (!empty($path[0])) {
			$page = $path[0];
		}
		if (!empty($path[1])) {
			$subpage = $path[1];
		}
		if (!empty($path[$count - 1])) {
			$title_for_layout = Inflector::humanize($path[$count - 1]);
		}


		if($page == "turnos"){

			$ruta = end( explode('/',$_SERVER["REQUEST_URI"]) ); 
			if (substr($ruta,0,4)=='page'){
				$pagina = end( explode(':',$ruta) ); 
			} else {
				$pagina = 1;
			}


			$cuantos_por_pagina = 20;
			$desde = 0;
			$hasta = $cuantos_por_pagina;

			// Busco a todos los Clientes
            $this->Client->unbindModel(array('hasMany' => array('Evolution','Recomendation','PeoplePractice','PeopleProblem','PeopleRecomendation',
																'PeopleSign','PeopleVademecum','Person','Result','Turn','License','ClientsPerson',
																'ClientsSponsor','Combo','RolsUser','ClientsProduct','ClientsFeriado','ClientsUser')	,
											 'belongsTo' => array('Stateclient'),
											)	);            


			if(!empty($_POST)){

				$pais_id      = $_POST['pais_id'];
    			$provincia    = $_POST['provincia'];
    			$localidad    = $_POST['localidad'];
    			$consultorio  = $_POST['consultorio'];
    			$especialidad = $_POST['especialidad'];


    			$this->set('pais_id',$pais_id);
    			$this->set('provincia',$provincia);
    			$this->set('localidad',$localidad);
    			$this->set('consultorio',$consultorio);
    			$this->set('especialidad',$especialidad);

    			if(!empty($provincia)) {
    				$cond_pcia = "and clients.provincia='$provincia'" ;
    			} else {
    				$cond_pcia = '';
    			}

    			if(!empty($localidad)) {
    				$cond_localidad = "and clients.localidad='$localidad'" ;
    			} else {
    				$cond_localidad = '';
    			}

    			if(!empty($consultorio)) {
    				$cond_consultorio = "and clients.name like '%$consultorio%'" ;
    			} else {
    				$cond_consultorio = '';
    			}


    			if ($especialidad>0){
    				$sql = "select specialties_users.user_id, clients_users.client_id
    						from 
    							specialties_users
    							left join clients_users on specialties_users.user_id = clients_users.user_id
    						where
    							specialties_users.specialty_id='$especialidad' 
    							and clients_users.staterecord_id='1'
    						group by
    							clients_users.client_id";
					$medicos = $this->SpecialtiesUser->query($sql);
					$lista_centros = '';
					if ($medicos) {
						foreach($medicos as $m){
							$lista_centros.=$m['clients_users']['client_id'].',';
						}	
						$lista_centros = substr($lista_centros, 0,-1);
						$lista_centros.='';
					}
					$filtro_especialidad = "and clients.id IN ($lista_centros)";
    			} else {
    				$lista_centros='';
    				// $filtro_especialidad = ' and clients.id in (select t1.client_id from products_users AS t1 INNER JOIN clients AS t2 ON t1.client_id = t2.id WHERE t2.stateclient_id = 1 group by t1.client_id)';
    				$filtro_especialidad = '';
    			}

				$user_last_login = date("Y-m-d", mktime(0,0,0,date("m"),date("d")-60,date("Y")));
				
	            $sql = "
					select 
						count(clients.id) AS tasa_uso, clients.*
					from 
						clients 
						inner join clients_users ON clients_users.client_id = clients.id
						INNER JOIN agendas ON agendas.client_id = clients.id AND agendas.user_id = clients_users.user_id
						INNER JOIN agendas_segments ON agendas_segments.agenda_id = agendas.id
						INNER JOIN users ON users.id = clients_users.user_id
					where 
						clients.id NOT IN (1182) 
						AND clients.country_id = '$pais_id' 
						AND clients.stateclient_id = 1 
						AND users.last_login >= '$user_last_login' 
						AND agendas.staterecord_id = 1 AND agendas.alcance = 'pub'  
						$filtro_especialidad $cond_pcia $cond_localidad $cond_consultorio
					GROUP BY
						clients.id	
					order by 
						tasa_uso DESC, clients.name ASC
	            ";
	            $registros = $this->Client->query($sql);
				
				$this->set('busqueda_realizada',1);

			} else {
				$pais_id = 1;
    			$this->set('pais_id',$pais_id);
    			$this->set('provincia','');
    			$this->set('localidad','');
    			$this->set('consultorio','');
    			$this->set('especialidad',0);
    			$this->set('busqueda_realizada',0);

				$registros = array();
			}

		
            $cuantos = count($registros);
			
			//-----------------------------------------------------
			//      Obtengo las sucursales de los clientes elegidos
			//-----------------------------------------------------
			foreach($registros as $clave=>$valor){
			
				$cliente_id = $valor['clients']['id'];
				
				$sql = "select Sucursal.*
						from sucursals as Sucursal
						inner join agendas as a on Sucursal.id=a.sucursal_id and a.alcance='pub' and staterecord_id='1'
						where Sucursal.client_id='$cliente_id'
						group by Sucursal.id";
/*				
				$sucursales = $this->Sucursal->find('all',array(
											'conditions' => array('Sucursal.client_id'=>$cliente_id)
											));
*/											
				$sucursales = $this->Sucursal->query($sql);
				$registros[$clave]['sucursales'] = $sucursales;
				
			}
			//pr($registros);
			
            $cantidad_de_paginas = $cuantos/$cuantos_por_pagina;

            $datos_generales = array();
            if(!empty($registros)){
				foreach($registros as $clave=>$valor){
					if ($clave>=$desde and $clave<$hasta){
						$datos_generales[] = $valor;
					}
				}
            }
//pr($datos_generales);
			$this->set('datos_generales', $datos_generales);
			$this->set('cuantos_por_pagina', $cuantos_por_pagina);
			$this->set('cantidad_de_paginas', $cantidad_de_paginas);
			$this->set('pagina', $pagina);
			$this->set('total_registros', $cuantos);			


			$countries = $this->Country->find('list', array('order' => array('Country.name')));
			$this->set(compact('countries'));

			$specialties = $this->Specialty->find('list', array('order' => array('Specialty.name')));
			$this->set(compact('specialties'));
		}

		// Defino el layout por default
		$this->layout = 'default_front';

		// Defino el Template a usar
		if($page == "home"){
	        $this->layout = 'default_front_home';
	    }
		if($page == "movistar"){
	        $this->layout = 'movistar';
		}

		$this->set(compact('page', 'subpage', 'title_for_layout'));
		$this->render(implode('/', $path));
	}

	public function beforeFilter() {
		$this->Auth->allow('*');
	}
}